Curso 3 Limpieza y recolección de datos (Datawrangling)

VOLVER

Curso 3 Limpieza y recolección de datos (Datawrangling)

Raw data VS. Datos Procesados
Ficheros
Archivos Excel
Leer XML
Leer JSON
DATA.TABLE
Acceso a base de datos
Acceos a HDF5
Expresiones regulares
Trabajar con fechas
Lubridate
DPLYR VS DATAFRAME
CREAR COLUMNA
AGRUPAR
ORDENAR
SELECCIONAR
FILTRAR

Raw data VS. Datos Procesados

El objetivo del curso es el siguiente:

Raw data $\rightarrow$ script de procesamiento $\rightarrow$ tidy data $\rightarrow$ data analysis $\rightarrow$ data communication

Tidy data

Cada variable que se mide debe estar en una columna
Cada observación diferente debe estar en una sóla fila
Debe haber una tabla por cada tipo de variable
Si tenemos múltiples tablas, deben incluir una columna en la tabla que les permita enlazarlos.

Trucos:

Incluir a fila al inicio con los nombres de las variables
Hacer los nombres de las variables legibles
En general, los datos deben ser salvados un archivo por cada tabla.

CodeBook

Información sobre las variables (unidades incluidas!!) del dataset no contenido en el tidy data
Información sobre las sumarizaciones que se han llevado a cabo
Información sobre el estudio experimental que se ha usado

Su formato habitual es word/text file. Debe haber una sección llamada “Diseño del estudio” que debe tener una información detallada de cómo se ha recolectado la información, y por último debe tener una sección llamada “Code Book” que describa cada variable y sus unidades.

Instruction list

idealmente es un script en R o en Python, el input del scrip es el raw data y la salida es el tidy data procesado. No debe tener parámetros.

En algunos casos no será posible hacer un script de con algunos de los pasos, en ese caso se debe proporcionar información del tipo:

Paso 1: Tomar el archivo de RawData y ejecutarlo con el software X
Paso 2: Coger la columna tres del archivo de salida y...
....

Ficheros

Un primer caso habitual en la recogida de datos, será descargar de internet dicho fichero de datos. A continuación se describe el mejor proceso para hacerlo.

Descarga de ficheros

Establecer working directory
- Relativo: setwd("./data"), setwd("../") = move up in directory
- Absoluto: setwd("/User/Name/data")
Comprobar si existe el fichero y descargarlo
- if(!file.exists("./data")) {dir.create("./data")}
Descarga
- download.file(url, destfile= "directory/filname.extension", method = "curl")
  - method = "curl" [mac only for https]
- dateDownloaded <- date() = record the download date
Leer el fichero y cargar los datos
- read.table() = necesita especificar file, header, sep, row.names, nrows
  - read.csv() = automaticamente establece sep = "," and header = TRUE
- quote = "" = no quotes (extremely helpful, common problem)
- na.strings = establecer el carácter que representa NA
- nrows = cuántas filas leer
- skip = cuántas líneas saltar
- col.names = especificar el nombre de las columnas
- check.names = TRUE/FALSE = si está a TRUE, los nombres de las variables en el dataframe son chequeadas para asegurar que son nombres de variables sintácticamente válidos y no están duplicados, y si no es así intenta adecuarlo

qownnotes-media-Bj1784

qownnotes-media-jX1784

qownnotes-media-fa1784

qownnotes-media-mC1784

qownnotes-media-fX1784

qownnotes-media-Wu1784

Archivos Excel

xlsx package: read.xlsx(path, sheetIndex = 1, ...)
- colIndex, rowIndex = se usa para leer ciertas filas y columnas
write.xlsx() = escribir el fichero a disco
read.xlsx2() = más rápido que read.xlsx() pero instable para leer subconjuntos de filas
XLConnect package más opciones para escribir/manipular archivos excel

qownnotes-media-sQ1784

qownnotes-media-XC1784

qownnotes-media-Is1784

Leer XML

XML = extensible markup language
frequented used to store structured data, widely used in Internet apps
extracción de XML = base para la mayoría de webscrapping
componentes
- markup = etiquetas que dan la estructura
- contenido = texto real del documento
tags = <section>, </section>, <line-break />
elements = <Greeting> test </Greeting>
attributes = <image src ="a.jpg" alt = "b">
leer archivo en R
- library(XML)
- doc <- xmlTreeParse(fileUrl, useInternal = TRUE) = loads data
- rootNode <- xmlRoot(doc) = wrapper element for entire document
- xmlName(rootNode) = returns name of the document
- names(rootNode) = return names of elements
- rootNode[[1]] = access first elements, similar to list
- rootNode[[1]][[1]] = first sub component in the first element
- xmlSApply(rootNode, xmlValue) = returns every single tagged element in the doc
XPath (new language)
- get specific elements of document
- /node = top level node
- //node = node at any level
- node[@attr-name = 'bob'] = node with attribute name
  - xpathSApply(rootNode, "//name", xmlValue) = get the values of all elements with tag “name”
  - xpathSApply(rootNode, "//price", xmlValue) = get the values of all elements with tag “price”
extraer contenido por atributos
- doc <- htmlTreeParse(url, useInternal = True)
- scores <- xpathSApply(doc, "//li[@class='score']", xmlvalue) = busca elementos li con class = "score" y devuelve su valor

qownnotes-media-jb1784

qownnotes-media-Gj1784

qownnotes-media-lK1784

qownnotes-media-cN1784

Otro ejemplo

qownnotes-media-dB1784

Leer JSON

JSON = JavaScript Object Notation
almacenamiento ligero de datos, formato común para datos desde una API
similar a XML en estructura pero diferente sintaxis
los datos se pueden guardar como:
- numbers (double)
- strings (double quoted)
- boolean (true/false)
- array (ordered, comma separated enclosed in [])
- object (unordered, comma separated collection of key/value pairs enclosed in {})
jsonlite package (json vignette can be found in help)
- library(jsonlite) = loads package
- data <- fromJSON(url) = strips data
  - names(data$owner) = returns list of names of all columns of owner data frame
  - data$owner$login = returns login instances
- data <- toJSON(dataframe, pretty = TRUE) = converts data frame into JSON format
  - pretty = TRUE = formats the code nicely
- cat(data) = prints out JSON code from the converted data frame
- fromJSON() = converts from JSON object/code back to data frame

qownnotes-media-Zt1784

qownnotes-media-Eu1784

qownnotes-media-Kl1784

qownnotes-media-wn1784

DATA.TABLE

Es una versión más rápida y eficiente que los data.frames en algunas ocasiones.

hereda de data.frame (external package) $\rightarrow$ todas las funciones que aceptan data.frame funcionan con data.table
puede ser (escrito en C), mucho mucho más rápido agrupando,subsetting y actualizando
sintaxis: dt <- data.table(x = rnorm(9), y = rep(c("a","b","c"), each = 3), z = rnorm(9))
tables() = returns all data tables in memory
- shows name, nrow, MB, cols, key
some subset works like before = dt[2, ], dt[dt$y=="a",]
- dt[c(2, 3)] = subset by rows, rows 2 and 3 in this case
Subconjuntos de colulmnas (modified for data.table)
- argument after comma is called an expression (collection of statements enclosed in {})
- dt[, list(mean(x), sum(z))] = returns mean of x column and sum of z column (no "" needed to specify column names, x and z in ejemplo)
- dt[, table(y)] = get table of y value (perform any functions)
añadir nuevas columnas
- dt[, w:=z^2]
  - when this is performed, a new data.table is created and data copied over (not good for large datasets)
- dt2 <- dt; dt[, y:= 2]
  - when changes are made to dt, changes get translated to dt2
  - Note: if copy must be made, use the copy() function instead
operaciones múltiples
- dt[, m:= {temp <- (x+z); log2(temp +5)}] $\rightarrow$ adds a column that equals log2(x+z + 5)
plyr like operations
- dt[,a:=x>0] = creates a new column a that returns TRUE if x > 0, and FALSE other wise
- dt[,b:=mean(x+w), by=a] = creates a new column b that calculates the aggregated mean for x + w for when a = TRUE/FALSE, meaning every b value is gonna be the same for TRUE, and others are for FALSE
variables especiales
- .N = returns integer, length 1, containing the number (essentially count)
  - dt <- data.table (x=sample(letters[1:3], 1E5, TRUE)) = generates data table
  - dt[, .N, by =x] = crea una tabla para contar observaciones agrupadas en x
keys (filtrar/subconjuntos rápidos)
- ejemplo: dt <- data.table(x = rep(c("a", "b", "c"), each = 100), y = rnorm(300)) = generates data table
  - setkey(dt, x) = establece la key en la columna x
  - dt['a'] = devuelve un data frame, donde x = ‘a’ (effectively filter)
joins (merging tables)
- ejemplo: dt1 <- data.table(x = c('a', 'b', ...), y = 1:4) = generates data table
  - dt2 <- data.table(x= c('a', 'd', ...), z = 5:7) = generates data table
  - setkey(dt1, x); setkey(dt2, x) = sets the keys for both data tables to be column x
  - merge(dt1, dt2) = devuelve una tabla, combina las dos tablas usando la columna x filtrando sólo los valores que coinciden entre las dos columnas x y los junta
lectura rápida de ficheros
- ejemplo: big_df <- data.frame(rnorm(1e6), rnorm(1e6)) = generates data table
  - file <- tempfile() = generates empty temp file
  - write.table(big_df, file=file, row.names=FALSE, col.names = TRUE, sep = "\t", quote = FALSE) = escribe los datos generados de big_df en tempfile
  - fread(file) = lee el archivo y carga los datos mucho más rápido que read.table()

qownnotes-media-mH5784

qownnotes-media-td5784

qownnotes-media-Ns5784

qownnotes-media-xr5784

qownnotes-media-bu5784

qownnotes-media-HL5784

qownnotes-media-Mc5784

qownnotes-media-RF5784

qownnotes-media-dl5784

qownnotes-media-LS5784

qownnotes-media-Iz5784

Una manera de hacer subconjuntos muy rápido es crear claves

qownnotes-media-Ia5784

qownnotes-media-eV5784

qownnotes-media-xN5784

https://stackoverflow.com/questions/13618488/what-you-can-do-with-data-frame-that-you-cant-in-data-table

Acceso a base de datos

MYSQL

qownnotes-media-mC5784

qownnotes-media-Nw5784

qownnotes-media-af5784

qownnotes-media-tc5784

qownnotes-media-Wf5784

qownnotes-media-cO5784

RODBC

#RJDBC will run quicker because it converts date to character and everything else to numeric. 
#RODBC will try to preserve the data type of the SQL table.

#If RODBC is very slow, would make sure that your R timezone - sys.setenv(TZ='GMT') set to GMT for example 
#- is same as the time zone of the SQL server from where you are pulling data. 
#It could be that the date column is taking a long time to be interpreted especially if it has a timestamp.

#See performance query 
#system.time(df <- sqlQuery(cn, query))


#RODBC

install.packages("RODBC")
library(RODBC)
#DD1 would be the DSN 
con <- odbcConnect("DD1", uid="rquser", pwd="rquser", rows_at_time = 500)
sqlSave(con,test_table, "TEST_TABLE")
sqlQuery(con,"select count(*) from TEST_TABLE")
d <- sqlQuery(con, "select * from TEST_TABLE")
close(con)

RORACLE

##RORACLE
##Special instructions are needed for installing RORacle, since it needs to be downloaded and compiled.
library(RCurl)
zipi <- getURLContent("http://download.oracle.com/otn/nt/roracle/ROracle_1.3-1.zip")
install.packages(zipi, repos = NULL)

#Download binary from oracle: http://www.oracle.com/technetwork/database/database-technologies/r/roracle/downloads/index.html
setwd('C:\\Users\\MH026898\\Downloads')   # set to path of download
install.packages('ROracle_1.3-1.zip', repos = NULL)
#Then load the library and use the package - you may have to change XXXX to whatever is in your TNS Names:

library(ROracle)
drv <- dbDriver("Oracle")
con <- dbConnect(drv, "rquser", "rquser")
dbWriteTable(con,"TEST_TABLE", test_table)
dbGetQuery(con,"select count(*) from TEST_TABLE")
d <- dbReadTable(con, "TEST_TABLE")
dbDisconnect(con)


library('ROracle')
drv <- dbDriver("Oracle")
con <- dbConnect(drv, "USER GOES HERE", "PASSWORD GOES HERE", dbname='XXX')
#test connection:
dbReadTable(con, 'DUAL')

#Use conection string instead of TNS NAMES
library('ROracle')
drv <- dbDriver("Oracle")
host <- "10.181.85.198"
port <- 1521
sid <- "cws"

connect.string <- paste(
  "(DESCRIPTION=",
  "(ADDRESS=(PROTOCOL=tcp)(HOST=", host, ")(PORT=", port, "))",
  "(CONNECT_DATA=(SID=", sid, ")))", sep = "")

## Use username/password authentication.
con <- dbConnect(drv, "reports", "reports", dbname=connect.string)
##test connection:
  
rs <- dbSendQuery(con, "select * from parametros_birt")
## We now fetch records from the resultSet into a data.frame.
data <- fetch(rs)       ## extract all rows
dim(data)
dbDisconnect(con)

RJDBC

#USING RJDBC

ConnectDB = function(){
  library(rJava)
  library(RJDBC)
  .jinit()
  drv <- JDBC(driverClass="oracle.jdbc.OracleDriver", classPath="jdbcDrivers/Oracle/ojdbc6.jar")
  drv <- JDBC(driverClass="oracle.jdbc.OracleDriver", classPath="./ojdbc6.jar")
  con <- dbConnect(drv, "jdbc:oracle:thin:@//IPADRESS:PORT/SERVICENAME", "user", "passw")
  return(con)
}
con <- ConnectDB()

#It can be used other drivers for jdbc

library(RJDBC)
drv <- JDBC("com.microsoft.sqlserver.jdbc.SQLServerDriver","/sqljdbc4.jar") 
con <- dbConnect(drv, "jdbc:sqlserver://server.location", "username", "password")
dbGetQuery(con, "select column_name from table")

Acceos a HDF5

qownnotes-media-CC5784

qownnotes-media-ZB5784

qownnotes-media-aO5784

qownnotes-media-zd5784

qownnotes-media-bP5784

qownnotes-media-Wn5784

qownnotes-media-Uc5784 Webscraping ————- qownnotes-media-VN5784

qownnotes-media-zT5784

qownnotes-media-Cf5784

qownnotes-media-pX5784

qownnotes-media-hM5784

qownnotes-media-XE5784

qownnotes-media-UB5784

qownnotes-media-ec5784

qownnotes-media-Oz5784

Otros recursos

qownnotes-media-it5784

qownnotes-media-kU5784

qownnotes-media-Ei5784

qownnotes-media-gL5784

Expresiones regulares

RegEx = combinación de literales y metacaracteres
usado junto a las funciones grep/grepl/sub/gsub o cualquier otra que suponga burcar cadenas
^ = comienzo de línea (metacharacter)
- ejemplo: ^text detecta “text …”
$ = final de línea (metacharacter)
- ejemplo: text$ detecta “… text”
[] = conjunto de caracteres que serán aceptados en la detección (character class)
- ejemplo: ^[Ii] detecta “I …” or “i …”
[0-9] = buscar por un rango de caracteres (character class)
- ejemplo: [a-zA-Z] detecta cualquier letra mayúscula o minúscula
[^?.] = cuando se usa al inicio de un character class, “^” significa anular el metacaracter (metacharacter)
- ejemplo: [^?.]$ detecta cualquier lúna que no acabe en en “.” or “?”
. = cualquier carácter (metacharacter)
- ejemplo: 9.11 detecta 9/11, 9911, 9-11, etc
| = or, usado para combinar subexpresiones llamadas alternatives (metacharacter)
- ejemplo: ^([Gg}ood | [Bb]ad) detecta cualquier línea que empiece con may/min “Good…” and “Bad …”
- Nota: () limita el alcance de las alternatives separadas por “|” aquí
? = expresión opcional = 0/1 de algún carácter o expresión (metacharacter)
- ejemplo: [Gg]eorge( [Ww]\.)? [Bb]ush detecta “george bush”, “George W. Bush”
- Nota: “" se añade antes de”." porque “.” es un metacarácter, “.” se llama punto escapado, le dice a la expresion que lea realmete un punto en lugar del operador
* = cualquier número de repetición, incluyendo ninguna = 0 o más de algún carácter/expresión (metacharacter)
- ejemplo: .* detecta cualquier combinación de caractéres
- Nota: * es ambicioso = siempre detecta la cadena más larga que satisface la expression regular
  - greediness of * puede anularse con ?
  - ejemplo: s.*?s detecta el texto más corto “s…s”
+ = 1 o más repeticiones = 1 o más carácter/expresión (metacharacter)
- ejemplo: [0-9]+ detecta al menos 1 dígito de números como “0”, “90”, or “021442132”
{m, n} = cuantificador de intervalo, permite especificar el mínimo y máximo número de detecciones (metacharacter)
- m = mínimo, n = no más de
- {m} = exactamente m detecciones
- {m, } = al menos m detecciones
- ejemplo: Bush( +[^ ]+ +){1, 5} debates detecta “Bush + (al menos 1 espacio + cualquier palabra que no contenga espacio + al menos 1 espacio) repiendo este patrón entre 1 y 5 veces + debates”
() = define un grupo como el siguiente entre paréntesis, los grupos se recuerdan y pueden ser referidos como \1, \2, etc.
- ejemplo: ([a-zA-Z]+) +\1 + detecta “cualquier palabra + al menos 1 espacio + la misma palabra repetida + al menos un espacio” = “night night”, “so so”, etc.

Trabajar con fechas

date() = devuelve fecha actual en formato carácter
Sys.Date() = devuelve fecha actual en formato Date
format(object, "format") = da formato a un objeto con el patrón
- cuano el objecto = Date:
  - %d = day as number (0-31)
  - %a = abbreviated weekday
  - %A = unabbreviated weekday
  - %m = month (00-12
  - %b = abbreviated month
  - %B = unabbreviated month
  - %y = 2 digit year
  - %Y = 4 digit year
- ejemplo: format(Sys.Date(), "%a %b %d") = devuelve “Sun Jan 18”
as.Date("character", "format") = convierte el string en Date formateando
- ejemplo: z <- as.Date("1jan1960", "%d%b%Y") = crea la fecha “1960-01-01”
Date1 - Date2 = diferencia en día imprimiendo “Time difference of n days”
- as.numeric() devuelve la diferencia en número
weekdays(Date), months(Date) = devuelve el día de la semana/mes
julian(Date) = COnvierte la fecha en el número de días que han pasado desde el origen
- attr(, "origin") = imprime el origen en formato fecha, que es 1970-01-01
lubridate package [library(lubridate)]
- ?Sys.timezone = documentación de cómo determinar timezones

Lubridate

consistent, memorable syntax for working with dates
wday(date, label = TRUE) = devuelve el número 1 - 7 representando Sunday - Saturday, o devuelve tres letras del día si label = TRUE
today(), now() = devuelve día y hora actual, con partes extraíbles (hour(), month())
- tzone = "America/New_York" = usado para especificar time zones (list here)
ymd("string") = convierte un string en year month day format a una variable de tiempo POSIXct
- mdy("string") = parses date in month day year format
- dmy(2508195) = parses date in day month year format using a number
- ymd_hms("string") = parses the year month day, hour minute second
- hms("string") = parses hour minute second
  - tz = "" = can use the “tz” argument to specify time zones
- Nota: existe uan variedad de funciones disponibles para parsear distintos formatos, todos ellos capaces de convertir la información correcta si el orden de mes dia año es correcto
- Nota: cuando sea necesario, // o ' deber ser añadido para clarificar
update(POSIXct, hours = 8, minutes = 34, seconds = 55) = acutaliza componentes de una hora
- Nota: does not alter the date time passed in unless explicitly assigned
arithmetic can be performed on date times by using the days() hours() minutes(), etc. functions
- ejemplo: now() + hours(5) + minutes(2) = returns the date time for 5 hours and 2 minutes from now
with_tz(time, tone ="") = return date-time in a different time zone
as.period(new_interval(last_time, arrive)) = return the properly formatted difference between the two date times

DPLYR VS DATAFRAME

CREAR COLUMNA

Dataframe

data$nuevoNombre <- ifelse(data$name=='TORNADO';'TOR';data$name)

Dplyr

data %>% 
    mutate(nuevoNombre =  ifelse(data$name=='TORNADO';'TOR';data$name))

msleep %>% 
    mutate(rem_proportion = sleep_rem / sleep_total)

AGRUPAR

Agregate

INJ <- aggregate(INJURIES ~ EVTYPE, data, FUN = sum)

Data table

DT <- data.table(data)
INJ <- DT[,sum(INJURIES),by=EVTYPE]

Dplyr

INJ <-  data %>% groupby(EVTYPE)%>%summarise(total.sum=sum(INJURIES)

ORDENAR

DataFrame

INJ <- INJ[order(-INJ$V1),]

Dplyr

INJ <- arrange(INJ, desc(V1))

# grouped arrange ignores group
INJ %>% arrange(desc(V1))
# Unless you specifically ask:
INJ %>% arrange(desc(V1), .bygroup = TRUE)

SELECCIONAR

Dataframe


event <- c("EVTYPE", "FATALITIES", "INJURIES", "PROPDMG", "PROPDMGEXP", "CROPDMG", 
           "CROPDMGEXP")
data <- storm[event]

dplyr

data <- select(storm, EVTYPE, FATALITIES,INJURIES,PROPDMG)

FILTRAR

Dataframe

data$CROPEXP[data$CROPDMGEXP == "2"]

dplyr

filter(data, CROPDMGEXP >= 16)